package com.desay.pangoo.itmt.repository;

import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.desay.pangoo.itmt.controller.vo.AllTechVo;
import com.desay.pangoo.itmt.controller.vo.MaturityVo;
import com.desay.pangoo.itmt.controller.vo.PriorityVo;
import com.desay.pangoo.itmt.controller.vo.TechOnPlanActivitiesVo;
import com.desay.pangoo.itmt.controller.vo.TechTotalVo;
import com.desay.pangoo.itmt.entity.TechProjectInfoEntity;

/**
 * @author uidq1163
 */
@Repository
public interface TechProjectInfoRepository extends JpaSpecificationExecutor<TechProjectInfoEntity>,
		PagingAndSortingRepository<TechProjectInfoEntity, Long> {

	/**
	 * 查询技术项目编号
	 * 
	 * @param techPtn
	 * @return
	 */
	List<TechProjectInfoEntity> findByTechPtnLikeOrderByTechPtnDesc(@Param("techPtn") String techPtn);

	/**
	 * 根据项目编号获取项目信息
	 * @param techPtn
	 * @return
	 */
	TechProjectInfoEntity findByTechPtn(@Param("techPtn") String techPtn);

	/**
	 * 根据项目编号获取项目信息
	 * @param techPtns
	 * @return
	 */
	List<TechProjectInfoEntity> findAllByTechPtnIn(Collection<String> techPtns);

	/**
	 * 查询技术项目编号:技术名称已经存在
	 * 
	 * @param techName
	 * @param techPtn
	 * @return
	 */
	List<TechProjectInfoEntity> findByTechNameAndTechPtnLikeOrderByTechPtnDesc(@Param("techName") String techName,
			@Param("techPtn") String techPtn);

	/**
	 * 各工作组技术优先级状态统计
	 * 
	 * @return
	 */
	@Query(value = "SELECT COUNT(a.id) AS total, c.work_group_name as workGroupName, a.priority as priority FROM tech_project_info a "
			+ "LEFT JOIN tech_classification b ON a.tech_class_id = b.id "
			+ "LEFT JOIN work_group c ON b.work_group_id = c.id GROUP BY a.priority,c.work_group_name", nativeQuery = true)
	List<PriorityVo> getPrioritiesStatistics();

	/**
	 * 关键技术：技术状态统计:Maturity
	 * 
	 * @return
	 */
	@Query(value = "SELECT COUNT(a.id) AS total, c.work_group_name as workGroupName, a.maturity as maturity FROM tech_project_info a "
			+ "LEFT JOIN tech_classification b ON a.tech_class_id = b.id "
			+ "LEFT JOIN work_group c ON b.work_group_id = c.id "
			+ "WHERE a.tech_level=2 and a.maturity is not null "
			// + "and a.maturity not in ('suspended', 'cancelled') "
			+ "AND a.id IN ( SELECT * FROM lately_5_years_project_view) GROUP BY a.maturity,c.work_group_name", nativeQuery = true)
	List<MaturityVo> getKeyTechMaturityStatistics();

	/**
	 * 核心技术：核心:1，关键:2，通用:3，一般:4
	 * 
	 * @return
	 */
	@Query(value = "SELECT COUNT(a.id) AS total, c.work_group_name as workGroupName, a.maturity as maturity FROM tech_project_info a "
			+ "LEFT JOIN tech_classification b ON a.tech_class_id = b.id "
			+ "LEFT JOIN work_group c ON b.work_group_id = c.id "
			+ "WHERE a.tech_level=1 and a.maturity is not null "
			// + "and a.maturity not in ('suspended', 'cancelled') "
			+ "AND a.id IN (SELECT * FROM lately_5_years_project_view) GROUP BY a.maturity,c.work_group_name", nativeQuery = true)
	List<MaturityVo> getCoreTechMaturityStatistics();
	
	
	/**
	 * 项目信息统计
	 * 
	 * @return
	 */
	@Query(value = "SELECT * from tech_total_view", nativeQuery = true)
	List<TechTotalVo> getTechTotalStatistics();
	
	/**
	 * 各工作组技术开发启动状态
	 * 
	 * @return
	 */
	@Query(value = "SELECT total,status,year,work_group_name as workGroupName from tech_on_plan_activities_view", nativeQuery = true)
	List<TechOnPlanActivitiesVo> getTechOnPlanActivitiesStatistics();
	
	/**
	 * Overview of defined technologies across all workgroups
	 * 
	 * @return
	 */
	@Query(value = "SELECT sum(total) as total,year,work_group_name as workGroupName from tech_on_plan_activities_view group by year,work_group_name", nativeQuery = true)
	List<AllTechVo> getAllTechStatistics();
	
	
	/**
	 * 获取所有过期：状态为plan的项目ID
	 * 
	 * @return
	 */
	@Query(value = "SELECT * from due_date_project_veiw", nativeQuery = true)
	List<Long> getAllDueDateProjectIds();

	/**
	 * 获取近五年的项目
	 * @return
	 */
	@Query(value = "SELECT * FROM lately_5_years_project_view", nativeQuery = true)
	List<Long> getLately5YearsPprojectIds();

    List<TechProjectInfoEntity> findAllByCreateTimeBetween(Date begin, Date end);
}